Read the pdf version here.
Date and Time functions and formats can vary for different databases. In the article we shall compare Couchbase N1QL Date-Time functions with Oracle ISO Date format.
In Oracle, date format and timezones feature multiple data types, including DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE. The TIMESTAMP data type is an extension of the DATE type.
Date format in Oracle can be represented either as literals or as numeric values. The literal needs to be in a specified format. The format for the date times can be set using the NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT and the NLS_DATE_LANGUAGE parameters. (See table below for detailed comparison with examples.)
With Couchbase, Date-Time is done a little differently. All Dates and times should be stored as strings that follow the Extended ISO 8601 date time format. N1QL contains DATETIME functions that can be used to and extract these formatted strings. The DATE and TIME for a specific timezone can also be represented as a Unix timestamp in milliseconds. This essentially means that unlike Oracle, where the format of the input Date and time can change based on the values of the NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT, the format for dates in Couchbase follows a strict set. (See table below for detailed comparison with examples)
For example in Oracle timestamp format,
‘2008-DEC-25 17:30′ is a valid date given the NLS_DATE_FORMAT=’YYYY-MON-DD HH24:MI’
However to represent the same value in Couchbase the user needs to use one of the given formats (see here).
This will be 2008-12-25 17:30:00
In oracle fractional_seconds_precision is optional and specifies the number of digits in the fractional part of the SECOND datetime field. It can be a number (0 to 9) with the default being 6.
For example, in Oracle the time format can be given as
TIMESTAMP ‘YYYY-MM-DD HH24:MI:SS.FFF’
Using this we can define the timestamp to be ‘2006-01-02 15:04:05.999’
N1QL has support for fractional seconds similar to oracle. This is seen when using the format – “2006-01-02T15:04:05.999”. However N1QL supports 3 digit precision (nanosecond precision) and oracle supports upto 9 digit fractional second precision.
This means that if we specify the date “2006-01-02T15:04:05.999123456”, n1ql will round off to 3 digits and return “2006-01-02T15:04:05.999”.
For N1QL,
|
1 2 3 4 5 6 |
SELECT STR_TO_TZ("2006-01-02T15:04:05.999123456", 'America/Los_Angeles') as west; "results": [ { "west": "2006-01-02T15:04:05.999" } ] |
For Oracle,
|
1 |
SELECT TO_TIMESTAMP('25-DEC-2008 01:00:00.336123456', 'dd-mon-yyyy hh:mi:ss.ff') as D from dual; |
| D |
| 2008-12-25 01:00:00.336123456 |
For N1QL, If we specify a more than 9 digits, the Date-Time function returns null.
|
1 2 3 4 5 6 |
SELECT STR_TO_TZ("2006-01-02T15:04:05.9991234567", 'America/Los_Angeles') as west; "results": [ { "west": null } ] |
For Oracle datetime format, if you give more than 9 digits for the fractional seconds part it throws an error – ORA-01830: date format picture ends before converting entire input string
A comparison of the Couchbase N1QL and Oracle’s date time support is given in the following following table:
| Oracle’s Date and Time datatypes | Couchbase N1QL’s date time format support | ||||||
| DATE (Data type)
Format defined by –
Example : The NLS_DATE_FORMAT parameter needs to be set in the initialization parameter file. NLS_DATE_FORMAT=’YYYY-MON-DD’ Sample date DATE ‘2008-DEC-25’ To set the NLS_DATE_LANGUAGE parameter we can use the ALTER SESSION statement.
|
In N1QL there is no specific date/time/timestamp data type. They are all represented by JSON strings with ISO 8601 extended formats and are manipulated using the date time manipulation and arithmetic functions.
– (see documentation for list) Allowed formats : (The date format) * YYYY-MM-DD Example:
In Couchbase/N1QL dates need to be represented in specific formats with day,month and year in numeric form. For all dates that do not match the input formats, we return the input date in the default format which is YYYY-MM-DDThh:mm:ss.sTZD. Let us use the function DATE_FORMAT_STR to convert the date format from YYYY-MM-DD to ‘YYYY-MON-DD’ (which in N1QL is an invalid format)
Couchbase supports only ISO Extended date formats. It doesn’t support non-numeric dates in multiple languages. |
||||||
| TIMESTAMP (Data Type)
Format defined by –
You can specify the value of NLS_TIMESTAMP_FORMAT by setting it in the initialization parameter file. NLS_TIMESTAMP_FORMAT = ‘YYYY-MM-DD HH:MI:SS.FF’ We can use the TO_TIMESTAMP function to convert input date-time to timestamp data type.
|
Same functions as Date but with a different input date format ( input argument to the function ). This needs to be explicitly provided. The timestamp formats are given below –
The date format along with timestamp components :
Only timestamp based formats –
Couchbase supports nanosecond precision. For Example:
|
||||||
| TIMESTAMP WITH TIMEZONE
TIMESTAMP WITH LOCAL TIME ZONE Data type Format defined by –
You can specify the value of NLS_TIMESTAMP_TZ_FORMAT by setting it in the initialization parameter file. NLS_TIMESTAMP_TZ_FORMAT = ‘YYYY-MM-DD HH:MI:SS.FF TZH:TZM’ We can use the TO_TIMESTAMP_TZ function to convert input date-time to timestamp with timezone data type. (It maintains the input timezone)
|
Timezone Manipulation functions – (see documentation for list)
Allowed formats : * Including the DateTime components of the format :
Only timestamp based formats –
With N1QL, in addition to the specific formats we also have specific timezone functions one of which is STR_TO_TZ which converts the input date to the specified timezone.
|
Table – Oracle DateTime types VS N1QL ISO Timezone and Date Formats
* Both Oracle and N1QL automatically determines whether Daylight Saving Time is in effect for a specified time zone and returns the corresponding local time.
** When dealing with the date formats in N1QL, it is important to remember that each component of the date time string need to be represented by a valid numeric value. So when passing in the date format to any N1QL functions, we need to pass the date as “2001-12-12” instead of “YYYY-MM-DD”. N1QL only supports the listed formats. Also the Date component of the date-time string has to be separated by “-” and the time components need to be separated by “:”. Otherwise it is not a valid Date.
For any date/time types both Oracle and N1QL store extra information in different fields for the input date. These allow the user to extract specific information about the date.
Oracle’s date-time fields are CENTURY, YEAR, MONTH, DAY, HOUR, MINUTE and SECOND. The TIMESTAMP data types represent seconds as fractional seconds with its precision is determined by the fractional_seconds_precision parameter. It also includes the fields TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION and TIMEZONE_ABBR. It internally converts the Oracle timestamp to date values. `The default time for the time component is midnight and the default date for the date component is the first day of the current month. A DATE datatype stores both the date and time information.
In addition to the fields that Oracle supports for its DATE and TIME data-types, N1QL also supports MILLENNIUM, DECADE, QUARTER, WEEK and MILLISECOND. The value of these fields is computed internally using basic arithmetic. N1QL does not support TIMEZONE_REGION and TIMEZONE_ABBR fields.
A detailed comparison for each field is given in the table below.
Let us consider the following sample row for our examples in timestamp format in Oracle:
|
1 2 |
create table t1 (date_purchased timestamp with time zone ); insert into t1 values (TIMESTAMP '2008-12-25 01:00:00.336 PST'); |
| t1 date_purchased |
| 2008-12-25,01:00:00.336-08:00 |
Let us consider the corresponding Couchbase document
Create bucket1 in Couchbase.
|
1 2 3 4 5 6 7 |
create primary index on bucket1; Insert into bucket1 values ("23", {"date_purchased":"2008-12-25T01:00:00.336-08:00"}); Bucket1 - Document id : 23 { "date_purchased":"2008-12-25T01:00:00.336-08:00" } |
The TO_CHAR (‘CC’ ) (with a date as the first arg) and EXTRACT function is used in Oracle to retrieve the date-time field values. For N1QL there are 2 functions DATE_PART_STR or DATE_PART_MILLIS depending on whether the date is represented as a JSON string or a numeric millisecond. We will use these functions to give examples for each date time field listed below.
| Date-Time fields/ parts | Oracle | N1QL | ||||
| CENTURY | ✅
|
✅
|
||||
| YEAR | ✅
|
✅
|
||||
| MONTH | ✅
|
✅
|
||||
| DAY | ✅
|
✅
|
||||
| HOUR
(different behaviour) |
✅
Oracle considers the timezone component of the input timestamp. |
✅
The difference in results is because N1QL does not consider the timezone component of the input timestamp. |
||||
| MINUTE | ✅
|
✅
|
||||
| SECOND | ✅
|
✅
(for fractional part see millisecond) |
||||
| TIMEZONE_HOUR | ✅
|
✅
|
||||
| TIMEZONE_MINUTE | ✅
|
✅
|
||||
| TIMEZONE_REGION | ✅
|
❌ | ||||
| TIMEZONE_ABBR | ✅
|
❌ | ||||
| TIMEZONE (offset from UTC) | (Data type itself, not a field) | ✅
The number here represents the timezone in seconds. |
||||
| MILLENNIUM | ❌ | ✅
Millennium = (Year / 1000) + 1
|
||||
| DECADE | ❌ | ✅
Decade = Year / 10
|
||||
| QUARTER | ❌ | ✅
Quarter = (Month + 2) / 3
|
||||
| WEEK | ❌ | ✅
Week = int(math.Ceil(float64(YearDay) / 7.0)) YearDay returns the day of the year specified by the time, in the range 1 to 365 for non-leap years, and 1 to 366 in leap years. (see golang time package)
|
||||
| MILLISECOND | ❌ | ✅
Millisecond = Nanosecond / 106 (as an integer)
|
||||
| ISO_YEAR | ❌ | ✅
Iso_year = ISO 8601 year for the input timestamp.
|
||||
| ISO_WEEK | ❌ | ✅
Iso_week = ISO 8601 week for the input timestamp. The week usually ranges from 1 to 53. For example, Jan 1 to Jan 3 of year n might belong to Week 52 or 53 of year n-1, and Dec 29 to 31 might belong to week 1 of year n+1.
|
||||
| DAY_OF_YEAR (DOY) | ❌ | ✅
Day_of_year or doy = YearDay (see golang time package) YearDay returns the day of the year specified by the time, in the range 1 to 365 for non-leap years, and 1 to 366 in leap years.
|
||||
| DAY_OF_WEEK (DOW) | ❌ | ✅
Day_of_week or dow = Weekday function, which returns the day of the week for the given time. (see golang time package)
|
For N1QL, within the date time format, TIMEZONE_REGION and TIMEZONE_ABBR are not supported. (But these are passed into the timezone specific N1QL functions which we shall see in Part 2 of this series).
As we can see above when it comes to representing the TIMESTAMP within N1QL dates, there are additional fields supported. These are ISO_YEAR, ISO_WEEK, DAY_OF_YEAR (DOY), DAY_OF_WEEK (DOW) and TIMEZONE which is the offset from UTC.
In the absence of a time zone indicator, the current local timezone is used (where the Couchbase server is installed).
Let us delve a little deeper into the TIMEZONE comparisons between N1QL and Oracle. The TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE data types in Oracle are variants of the TIMESTAMP data type. The former includes the time zone information, which is the time zone offset which is the time relative to UTC or time zone region name in its value, and the later includes the current session timezone. TIMESTAMP WITH LOCAL TIME ZONE does not store time zone information internally, but you can see local time zone information in SQL output if the TZH:TZM or TZR TZD format elements are specified. (see here for more details).
Oracle:
TIMESTAMP ‘2017-01-31 03:49:30.612 -08:00’
Couchbase:
“2017-01-31T03:49:30.612-08:00”
For Oracle, if two dates being compared represent the same value in UTC, then they are equal.
Oracle:
TIMESTAMP ‘2017-01-15 8:00:00 -8:00’ == TIMESTAMP ‘2017-01-15 10:00:00 -6:00’
In N1QL currently in order to compare full date values we need to convert them to milliseconds.
N1QL:
STR_TO_MILLIS(“2017-01-31T05:49:30.612-06:00”) == STR_TO_MILLIS(“2017-01-31T03:49:30.612-08:00”)
Value : 1485863370612
For Oracle, we can replace this offset with the Time zone region (TZR) and the abbreviation. The abbreviation (TZD) is used in the event the region value is ambiguous (when the US switches to daylight saving time).
However in N1QL the timezone component of the date is always represented as a UTC offset.
For example,
TIMESTAMP ‘2017-01-15 8:00:00 -8:00’ can also be TIMESTAMP ‘2017-01-15 8:00:00 US/Pacific PDT’
Oracle also supports interval data types INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. These store time durations. The former stores the duration using year and month fields and the latter using the days, hours, minutes and second fields. With N1QL, computing an interval is made easy using date time functions and the “part” component. (These functions will be explored more in Part 2).
There are multiple ways to insert date or time data into Oracle. The user can insert a formatted string based on the NLS format value, or a literal with explicit conversion using the TO_DATE / TO_TIMESTAMP or TO_TIMESTAMP_TZ functions or implicit conversion.
For N1QL, all dates are added to a document as a string in the format specified above, or as a number representing a Unix timestamp in milliseconds. This makes handling dates very easy since the functions perform all the necessary arithmetic for the user. One drawback though is that the date has to exactly match one of the formats in the Date formats table. This restricts the user to use only a subset of available formats.
One workaround for this limitation with N1QL is to use the string functions and massage the input date to reflect the format you want.
For example,
Convert 2016-09-23T18:48:11.000+00:00 into “YYYY/MM/DD”
|
1 2 3 4 5 6 7 |
SELECT to_string(date_part_str("2016-09-23T18:48:11.000+00:00", "year" )) || "/" || to_string(date_part_str("2016-09-23T18:48:11.000+00:00", "month" )) || "/" || to_string(date_part_str("2016-09-23T18:48:11.000+00:00", "day" )); "results": [ { "$1": "2016/9/23" } ] |
As we can see, N1QL simplifies manipulating Dates and Timestamps by representing it as a string or a number when compared to Oracle date to timestamp format. But this means that the user is restricted to use only specified date time formats and does not have the freedom to manipulate these formats, which Oracle does very easily using its Format Parameters.
Coming up in the Date Time article series –
- Datetime and Interval Arithmetic
- Conversion between different date time formats/data types.
- How Oracle and N1QL handle daylight savings
- Timezone related functions and how n1ql expects its timezone strings.
- General SQL/N1QL functions to retrieve timestamps.
[…] At SnapFactory we track everything using Google Analytics. This is terrific on our normal HTML pages but we can’t really track details on our portfolios. liveBooks has support for page-by-page tracking so we can measure what people are looking at and what we need to update. We also make sure to use the best data management software systems from websites like https://staging.couchbase.com/blog/dates-timestamps-timezones-comparative-study-oracle-n1ql-support-date-tim…. […]